﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;
using System.Data;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_SelectReports : IDAL_FIM_SelectReports
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable SumUpBorrow(string BgYear, string BgmProj, string EmpSeq, DateTime? StartDate, DateTime? EndDate, string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@BgYear", BgYear);
                dbManager.AddParameters(2, "@bgm_proj_seq", BgmProj);
                dbManager.AddParameters(3, "@debtor_emp_seq", EmpSeq);
                dbManager.AddParameters(4, "@start_date", StartDate);
                dbManager.AddParameters(5, "@end_date", EndDate);
                dbManager.AddParameters(6, "@bgm_source_seq", bgm_source_seq);

                strSQL = "SELECT r.money_req_date, r.borrow_no, CONCAT(px.prefix_name, ' ', emp.first_name , '  ', emp.last_name ) AS emp_name"
                    + " , r.money_req_name, r.borrow_start_date , r.borrow_end_date, pj.bgm_proj_name"
                    + " , CASE WHEN r.bgm_source_seq = 1 THEN rd.amount ELSE null END as amount1 "
                    + " , CASE WHEN r.bgm_source_seq = 11 THEN rd.amount ELSE null END as amount2 "
                    + ", p.real_pay_date2"
                    + ", DATEADD(day, 20, p.real_pay_date2) as return_due_date"
                    + ", rec.receive_date, rt.amount as return_amount, rec.amount as receive_amount "
                    + ", coalesce(rd.amount, 0) - coalesce(rt.amount, 0) - coalesce(rec.amount, 0) as remain_amount"
                    + ", case when coalesce(rd.amount, 0) - coalesce(rt.amount, 0) - coalesce(rec.amount, 0) > 0 then DATEDIFF(day, r.borrow_return_date, getdate()) else 0 end as remain_date"
                    + ", rec.receipt_no"
                    + " FROM fim_money_requests r "
                    + " LEFT JOIN (SELECT rd.money_req_seq, SUM(COALESCE(rd.amount, 0)) as amount "
                        + " FROM fim_money_request_dtls rd"
                        + " WHERE rd.record_status NOT LIKE @delete_status"
                        + " GROUP BY rd.money_req_seq "
                        + " ) rd ON r.money_req_seq = rd.money_req_seq "
                    + " LEFT JOIN v_payment p on r.money_req_seq = p.money_req_seq and p.record_status = 'A'"
                    + " LEFT JOIN hrm_ps_employees emp ON r.debtor_emp_seq = emp.emp_seq "
                    + " LEFT JOIN mas_name_prefixes px ON emp.name_prefix_seq = px.name_prefix_seq "
                    + " LEFT JOIN bgm_project_operation op ON r.bgm_proj_oper_seq = op.bgm_proj_oper_seq "
                    + " LEFT JOIN bgm_project pj ON op.bgm_proj_seq = pj.bgm_proj_seq "
                    + " LEFT JOIN (SELECT rt.money_req_seq "
                        + ", SUM(COALESCE(rd.amount, 0)) as amount "
                        + " FROM fim_money_return_dtls rd "
                        + " LEFT JOIN fim_money_returns rt on rd.return_seq = rt.return_seq "

                        + " WHERE rd.record_status NOT LIKE @delete_status "
                        + " and rt.record_status NOT LIKE @delete_status "
                        + " GROUP BY rt.money_req_seq "
                        + ") rt ON r.money_req_seq = rt.money_req_seq "

                    + "  LEFT JOIN (SELECT r.money_req_seq "
                        + ", SUM(COALESCE(r.amount, 0)) as amount "
                        + ", max(r.receive_date) as receive_date"
                        + ", STUFF(("
                            + " SELECT N', ' + CAST(f1.receipt_no AS VARCHAR(255))"
                            + " from fim_receives f1"
                            + " WHERE r.money_req_seq = f1.money_req_seq"
			                    + " and f1.record_status <> 'D'"
                            + " FOR XML PATH ('')), 1, 2, '') AS receipt_no"
                        + " FROM fim_receives r "
                        + " WHERE r.record_status NOT LIKE @delete_status "
                        + " GROUP BY r.money_req_seq "
                        + ") rec ON r.money_req_seq = rec.money_req_seq "
                    + " WHERE r.record_status = 'A'"
                    + " AND r.event_seq = 16"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (!string.IsNullOrEmpty(BgYear) ? " AND r.bgm_year = @BgYear" : "")
                    + (!string.IsNullOrEmpty(BgmProj) ? " AND pj.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(EmpSeq) ? " AND r.debtor_emp_seq = @debtor_emp_seq" : "")
                    + (StartDate != null ? " AND r.money_req_date >= @start_date" : "")
                    + (EndDate != null ? " AND r.money_req_date <= @end_date" : "")
                    + " order by r.money_req_date, r.borrow_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable SumUpPaymentCheque(string BgYear, string BankSeq, string BankAccSeq, DateTime? StartDate, DateTime? EndDate, string strChequeStatus)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", BgYear);
                dbManager.AddParameters(2, "@bank_seq", BankSeq);
                dbManager.AddParameters(3, "@bank_acc_seq", BankAccSeq);
                dbManager.AddParameters(4, "@start_date", StartDate);
                dbManager.AddParameters(5, "@end_date", EndDate);
                dbManager.AddParameters(6, "@cheque_status", strChequeStatus);

                strSQL = "select p.ref_no, p.pay_name"
                       + ", case when p.contact_seq is not null then con.NameTh"
                       + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                       + " end as payee_name"
                       + ",c.cheque_date, DATEADD(day, 90, c.cheque_date) as expire_date, c.actual_pay_date"
                       + ", cheque_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                       + ", case c.cheque_status when '1' then 'พร้อมใช้'"
                       + " when '2' then 'จ่ายแล้ว'"
                       + " when '3' then 'ขึ้นเงิน'"
                       + " else 'ยกเลิก'"
                       + " end as cheque_status_name"
                       + ", p.amount"
                       + " from fim_cheques c"
                       + " left join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                       + " left join fim_payments p on pc.pay_seq = p.pay_seq"
                       + " left join fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq"
                       + " left join fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq"
                       + " left join fim_mas_banks b on br.bank_seq = b.bank_seq"
                       + " left join IcmMasContact con on p.contact_seq = con.ConId"
                       + " left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq"
                       + " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                       + " where c.record_status not like @delete_status and p.record_status not like @delete_status"
                    //+ " and p.pay_seq is not null"
                       + (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                       + (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                       + (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                       + (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                       + (StartDate != null ? " AND c.cheque_date >= @start_date" : "")
                       + (EndDate != null ? " AND c.cheque_date <= @end_date" : "")
                       + " order by p.ref_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable SumUpPaymentCheque(string BgYear, string BankSeq, string BankAccSeq, string startChequeBookNo, string endChequeBookNo, string strChequeStatus)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", BgYear);
                dbManager.AddParameters(2, "@bank_seq", BankSeq);
                dbManager.AddParameters(3, "@bank_acc_seq", BankAccSeq);
                dbManager.AddParameters(4, "@start_cheque_book_no", startChequeBookNo);
                dbManager.AddParameters(5, "@end_cheque_book_no", endChequeBookNo);
                dbManager.AddParameters(6, "@cheque_status", strChequeStatus);

                strSQL = "select p.ref_no, p.pay_name"
                       + ", case when p.contact_seq is not null then con.NameTh"
                       + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                       + " end as payee_name"
                       + ",c.cheque_date, DATEADD(day, 90, c.cheque_date) as expire_date, c.actual_pay_date"
                       + ", cheque_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                       + ", case c.cheque_status when '1' then 'พร้อมใช้'"
                       + " when '2' then 'จ่ายแล้ว'"
                       + " when '3' then 'ขึ้นเงิน'"
                       + " else 'ยกเลิก'"
                       + " end as cheque_status_name"
                       + ", p.amount"
                       + " from fim_cheques c"
                       + " left join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                       + " left join fim_payments p on pc.pay_seq = p.pay_seq"
                       + " left join fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq"
                       + " left join fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq"
                       + " left join fim_mas_banks b on br.bank_seq = b.bank_seq"
                       + " left join IcmMasContact con on p.contact_seq = con.ConId"
                       + " left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq"
                       + " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                       + " left join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                       + " where c.record_status not like @delete_status and p.record_status not like @delete_status"
                    //+ " and p.pay_seq is not null"
                       + (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                       + (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                       + (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                       + (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                       + (" AND cb.cheque_book_no like @start_cheque_book_no")
                    //+ (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "")
                    //+ " order by p.ref_no"
                       + " UNION ALL"
                       + " select p.ref_no, p.pay_name"
                       + ", case when p.contact_seq is not null then con.NameTh"
                       + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                       + " end as payee_name"
                       + ",c.cheque_date, DATEADD(day, 90, c.cheque_date) as expire_date, c.actual_pay_date"
                       + ", cheque_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                       + ", case c.cheque_status when '1' then 'พร้อมใช้'"
                       + " when '2' then 'จ่ายแล้ว'"
                       + " when '3' then 'ขึ้นเงิน'"
                       + " else 'ยกเลิก'"
                       + " end as cheque_status_name"
                       + ", p.amount"
                       + " from fim_cheques c"
                       + " left join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                       + " left join fim_payments p on pc.pay_seq = p.pay_seq"
                       + " left join fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq"
                       + " left join fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq"
                       + " left join fim_mas_banks b on br.bank_seq = b.bank_seq"
                       + " left join IcmMasContact con on p.contact_seq = con.ConId"
                       + " left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq"
                       + " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                       + " left join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                       + " where c.record_status not like @delete_status and p.record_status not like @delete_status"
                    //+ " and p.pay_seq is not null"
                       + (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                       + (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                       + (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                       + (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                    //+ (startChequeBookNo != "" ? " AND cb.cheque_book_no like @start_cheque_book_no" : "")
                       + (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "");
                //+ " order by p.ref_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable SumUpPaymentChequeHeader(string BgYear, string BankSeq, string BankAccSeq
            , string startChequeBookNo, DateTime? StartDate, DateTime? EndDate, string strChequeStatus)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", BgYear);
                dbManager.AddParameters(2, "@bank_seq", BankSeq);
                dbManager.AddParameters(3, "@bank_acc_seq", BankAccSeq);
                dbManager.AddParameters(4, "@start_cheque_book_no", startChequeBookNo);
                dbManager.AddParameters(5, "@start_date", StartDate);
                dbManager.AddParameters(6, "@end_date", EndDate);
                dbManager.AddParameters(7, "@cheque_status", strChequeStatus);

                strSQL = "select c.*, cb.cheque_book_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                    + ", case c.cheque_status when '1' then 'พร้อมใช้' when '2' then 'จ่ายแล้ว' when '3' then 'ขึ้นเงิน' else 'ยกเลิก' end as cheque_status_name"
                    //+ ", case when p.contact_seq is not null then con.NameTh else concat(np.prefix_short, emp.first_name, ' ', emp.last_name) end as payee_name"
                    + " from fim_cheques c inner join"
                    //+ " fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq inner join"
                    + " fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq inner join"
                    + " fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq inner join"
                    + " fim_mas_banks b on br.bank_seq = b.bank_seq inner join"
                    //+ " fim_payments p on pc.pay_seq = p.pay_seq left join"
                    //+ " IcmMasContact con on p.contact_seq = con.ConId left join"
                    //+ " hrm_ps_employees emp on p.emp_seq = emp.emp_seq left join"
                    //+ " mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq left join"
                    + " fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                    + " where c.record_status not like @delete_status"
                    //+ " and p.pay_seq is not null"
                    //+ (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                    + (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                    + (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                    + (StartDate.HasValue ? " AND c.cheque_date >= @start_date" : "")
                    + (EndDate.HasValue ? " AND c.cheque_date <= @end_date" : "")
                    + (" AND cb.cheque_book_no = @start_cheque_book_no");
                //+ (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "")
                //+ " group by c.cheque_seq, cheque_no, cheque_book_no, b.bank_name, ba.bank_acc_no, c.cheque_status";
                //+ " UNION ALL"
                //+ " select cheque_no, cheque_book_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                //+ ", case c.cheque_status when '1' then 'พร้อมใช้' when '2' then 'จ่ายแล้ว' when '3' then 'ขึ้นเงิน' else 'ยกเลิก' end as cheque_status_name"
                ////+ ", case when p.contact_seq is not null then con.NameTh else concat(np.prefix_short, emp.first_name, ' ', emp.last_name) end as payee_name"
                //+ " from fim_cheques c full outer join"
                //+ " fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq inner join"
                //+ " fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq left join"
                //+ " fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq left join"
                //+ " fim_mas_banks b on br.bank_seq = b.bank_seq left join"
                //+ " fim_payments p on pc.pay_seq = p.pay_seq left join"
                //+ " IcmMasContact con on p.contact_seq = con.ConId left join"
                //+ " hrm_ps_employees emp on p.emp_seq = emp.emp_seq left join"
                //+ " mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq left join"
                //+ " fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                //+ " where c.record_status not like @delete_status"
                ////+ " and p.pay_seq is not null"
                ////+ (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                //+ (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                //+ (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                //+ (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                ////+ (startChequeBookNo != "" ? " AND cb.cheque_book_no like @start_cheque_book_no" : "")
                //+ (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "")
                //+ " group by cheque_no, cheque_book_no, concat(b.bank_name, ' / ', ba.bank_acc_no),case c.cheque_status when '1' then 'พร้อมใช้' when '2' then 'จ่ายแล้ว' when '3' then 'ขึ้นเงิน' else 'ยกเลิก' end";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable SumUpPaymentChequeDetail(string chequeSeq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cheque_seq", chequeSeq);

                strSQL = "select c.cheque_date, cheque_book_no, DATEADD(day, 90, c.cheque_date) as expire_date, c.actual_pay_date"
                    //+ ", cheque_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                    + ", case when p.contact_seq is not null then con.NameTh else concat(np.prefix_short, emp.first_name, ' ', emp.last_name) end as payee_name"
                    + ", p.ref_no, p.pay_name, p.bgm_year"
                    + ", p.amount"
                    + " from fim_cheques c"
                    + " left join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                    + " left join fim_payments p on pc.pay_seq = p.pay_seq"
                    //+ " left join fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq"
                    //+ " left join fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq"
                    //+ " left join fim_mas_banks b on br.bank_seq = b.bank_seq"
                    + " left join IcmMasContact con on p.contact_seq = con.ConId"
                    + " left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq"
                    + " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " left join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                    + " where p.record_status not like @delete_status"
                    + " and pc.record_status not like @delete_status"
                    //+ " and p.pay_seq is not null"
                    //+ (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                    //+ (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                    //+ (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                    //+ (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                    //+ " AND c.cheque_book_seq = @cheque_book_seq"
                    //+ (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "")
                    + " AND c.cheque_seq = @cheque_seq";
                //+ " UNION ALL"
                //+ " select c.cheque_date, cheque_book_no, DATEADD(day, 90, c.cheque_date) as expire_date, c.actual_pay_date"
                ////+ ", cheque_no, concat(b.bank_name, ' / ', ba.bank_acc_no) as bank_acc_name"
                //+ ", case when p.contact_seq is not null then con.NameTh else concat(np.prefix_short, emp.first_name, ' ', emp.last_name) end as payee_name"
                //+ ", p.ref_no, p.pay_name, p.bgm_year"
                //+ ", p.amount"
                //+ " from fim_cheques c"
                //+ " left join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                //+ " left join fim_payments p on pc.pay_seq = p.pay_seq"
                //+ " left join fim_mas_bank_accounts ba on c.bank_acc_seq = ba.bank_acc_seq"
                //+ " left join fim_mas_bank_branchs br on ba.branch_seq = br.branch_seq"
                //+ " left join fim_mas_banks b on br.bank_seq = b.bank_seq"
                //+ " left join IcmMasContact con on p.contact_seq = con.ConId"
                //+ " left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq"
                //+ " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                //+ " left join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                //+ " where c.record_status not like @delete_status and p.record_status not like @delete_status"
                ////+ " and p.pay_seq is not null"
                ////+ (!string.IsNullOrEmpty(BgYear) ? " AND p.bgm_year = @bgm_year" : "")
                //+ (!string.IsNullOrEmpty(BankSeq) ? " AND b.bank_seq = @bank_seq" : "")
                //+ (!string.IsNullOrEmpty(BankAccSeq) ? " AND c.bank_acc_seq = @bank_acc_seq" : "")
                //+ (!string.IsNullOrEmpty(strChequeStatus) ? " AND c.cheque_status = @cheque_status" : "")
                ////+ (startChequeBookNo != "" ? " AND cb.cheque_book_no like @start_cheque_book_no" : "")
                //+ (endChequeBookNo != "" ? " AND cb.cheque_book_no like @end_cheque_book_no" : "")
                //+ " AND cheque_no = @chequeno";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable MoneyRequestNotPay(string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                //dbManager.AddParameters(2, "@bank_seq", BankSeq);
                //dbManager.AddParameters(3, "@bank_acc_seq", BankAccSeq);
                //dbManager.AddParameters(4, "@start_date", StartDate);
                //dbManager.AddParameters(5, "@end_date", EndDate);
                //dbManager.AddParameters(6, "@cheque_status", strChequeStatus);

                strSQL = "select s.bgm_source_seq, s.bgm_source_name"
                    + ", pj.bgm_proj_seq, pj.bgm_proj_name"
                    + ", mr.money_req_seq, mr.ref_no, mr.money_req_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", mr.money_req_date"
                    + ", exp.bgm_exp_name, mrd.amount, smrd.sum_amount"

                    + " from fim_money_requests mr"
                    + " inner join fim_money_request_dtls mrd on mr.money_req_seq = mrd.money_req_seq"
                    + " inner join (select money_req_seq"
                        + ", sum(coalesce(amount, 0)) as sum_amount"
                        + " from fim_money_request_dtls"
                        + " where record_status not like @delete_status"
                        + " group by money_req_seq"
                        + ") smrd on mr.money_req_seq = smrd.money_req_seq"
                    + " inner join bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
                    + " inner join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
                    + " inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq"
                    + " inner join bgm_project pj on op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " inner join bgm_mas_source s on mr.bgm_source_seq = s.bgm_source_seq"
                    + " inner join bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq"
                    + " left join hrm_ps_employees emp on mr.emp_seq = emp.emp_seq"
                    + " left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " left join fim_payments p on mr.money_req_seq = p.money_req_seq"
                    + " left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq"
                    + " left join fim_cheques che on pc.cheque_seq = che.cheque_seq"
                    + " where mr.record_status = @approve_status"
                    + (bgm_source_seq != "" ? " and mr.bgm_source_seq like @bgm_source_seq" : "")
                    + " and mrd.record_status not like @delete_status"
                    + " and pc.record_status not like @delete_status"
                    + " and che.record_status not like @delete_status"
                    + " and che.cheque_status not like @delete_status"
                    + " and p.real_pay_date is null"
                    + " and che.pay_date is null"
                    + " order by s.bgm_source_seq, pj.bgm_proj_seq, mr.money_req_date, mr.money_req_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Receives()
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();

                strSQL = "SELECT fim_receives_log.receipt_no, fim_receives_log.ref_no, fim_receives_log.created_when AS receive_date, fim_receives.received_by, fim_receives_log.receive_mode, fim_receives_log.bgm_source_seq"
                        + ", fim_receives.exclude_vat, fim_receives.vat_amount, fim_receives.amount, fim_receives.discount, fim_receives_log.record_status"
                        + ", fim_mas_receive_types.receive_type_name, hrm_ps_employees_1.first_name + ' ' + hrm_ps_employees_1.last_name AS emp_name"
                        + @", iif(fim_receives.temp_contact is not null, fim_receives.temp_contact, iif(fim_receives.contact_seq is not null, IcmMasContact.nameth, hrm_ps_employees.first_name + ' ' + hrm_ps_employees.last_name)) as contact_name"
                        + " FROM IcmMasContact RIGHT OUTER JOIN"
                        + " fim_receives ON IcmMasContact.ConId = fim_receives.contact_seq LEFT OUTER JOIN"
                        + " hrm_ps_employees ON fim_receives.emp_seq = hrm_ps_employees.emp_seq LEFT OUTER JOIN"
                        + " fim_mas_receive_types ON fim_receives.receive_type_seq = fim_mas_receive_types.receive_type_seq LEFT OUTER JOIN"
                        + " hrm_ps_employees AS hrm_ps_employees_1 ON fim_receives.received_by = hrm_ps_employees_1.emp_seq RIGHT OUTER JOIN"
                        + " fim_receives_log ON fim_receives.receive_seq = fim_receives_log.receive_seq"
                        + " ORDER BY fim_receives_log.receipt_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Receives(DateTime? StartDate, DateTime? EndDate, string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@StartDate", StartDate);
                dbManager.AddParameters(2, "@EndDate", EndDate);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);

                strSQL = " SELECT r.receive_date, r.ref_no, r.receipt_no, r.received_by, r.amount, r.record_status "
                    + " , concat(emp.first_name , '  ', emp.last_name ) as emp_name"
                    + @", iif(r.temp_contact is not null, r.temp_contact,
		                    iif(r.contact_seq is not null, con.nameth, 
			                    emp2.first_name + ' ' + emp2.last_name)) as contact_name "
                    + " FROM fim_receives r "
                    + " LEFT JOIN hrm_ps_employees emp ON r.received_by = emp.emp_seq "
                    + " left join IcmMasContact con on r.contact_seq = con.ConId "
                    + " left join hrm_ps_employees emp2 on emp2.emp_seq = r.emp_seq"
                    + " WHERE receipt_no IS NOT NULL"
                     + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + " AND r.receipt_no <> ''"
                    + (StartDate != null ? " and r.receive_date >= @StartDate" : "")
                    + (EndDate != null ? " and r.receive_date <= @EndDate" : "")
                    + " ORDER BY r.receipt_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Receives(DateTime? StartDate, DateTime? EndDate, string bgm_source_seq, string receive_mode)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@StartDate", StartDate);
                dbManager.AddParameters(2, "@EndDate", EndDate);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@receive_mode", receive_mode);

                strSQL = " SELECT r.created_when as receive_date, r.ref_no, r.receipt_no, r.received_by,r.exclude_vat,r.vat_amount,r.amount,r.record_status "
                    + " , concat(emp.first_name , '  ', emp.last_name ) as emp_name, rt.receive_type_name"
                    + @", iif(r.temp_contact is not null, r.temp_contact,
		                    iif(r.contact_seq is not null, con.nameth, 
			                    emp2.first_name + ' ' + emp2.last_name)) as contact_name "
                    + " FROM fim_receives r "
                    + " LEFT JOIN hrm_ps_employees emp ON r.received_by = emp.emp_seq "
                    + " left join IcmMasContact con on r.contact_seq = con.ConId "
                    + " left join hrm_ps_employees emp2 on emp2.emp_seq = r.emp_seq"
                    + " left join fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                    + " WHERE receipt_no IS NOT NULL AND r.record_status not like 'H'"
                     + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + " AND r.receipt_no <> '' AND r.receive_mode = @receive_mode"
                    + (StartDate != null ? " and r.receive_date >= @StartDate" : "")
                    + (EndDate != null ? " and r.receive_date <= @EndDate" : "")
                    + " ORDER BY r.created_when, r.receipt_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Salary(string month, string year)
        {
            string strSQL;
            DataTable dtb = null;
            var convert = new ConvertNullable();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@month", month);
                dbManager.AddParameters(2, "@year", year);

                strSQL = @"
select pr.*, p.pay_date, p.ref_no, c.NameTh, prd.count_emp
from fim_payroll_sentpayment_combine pr
left join fim_payments p on pr.pay_seq = p.pay_seq
left join IcmMasContact c on p.contact_seq = c.ConId
left join (select [year], [month], count(*) as count_emp
    from fim_payroll_dtls
    group by [year], [month]
    ) prd on pr.year_no = prd.[year]
    and pr.month_no = prd.[month]
where pr.record_status <> @delete_status
and p.record_status <> @delete_status
and pr.month_no = @month
and pr.year_no = @year
";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }
    }
}
